import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.text.ExtendedMessageFormat;
import org.junit.Test;

import java.io.File;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class SetFieldType4Uighur {

	@Test
	public void 中文字段转换为维文字段() throws Exception {
		//ALTER TABLE [dbo].[Points] ALTER COLUMN [InUse] nvarchar(50) COLLATE Uighur_100_CI_AI
		Connection connection = TestSQLServerJDBC.getConnection();
		ResultSet columns = connection.getMetaData().getColumns(null, "dbo", null, null);
		ResultSetMetaData metaData = columns.getMetaData();
		System.out.print("行号\t");
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			String columnName = metaData.getColumnName(i + 1);
			System.out.print(columnName + "\t");
		}
		System.out.println();
		int row = 0;
		File file = new File("varchar2nvarchar-" + RandomStringUtils.randomNumeric(0) + ".txt");
		PrintStream ps = new PrintStream(file);
		while (columns.next()) {
			String type_name = columns.getString("TYPE_NAME");
			if (!"varchar".equals(type_name) && !"text".equals(type_name)) continue;
			row++;
			System.out.print(row + "\t");
			for (int i = 0; i < metaData.getColumnCount(); i++) {
				String columnName = columns.getString(i + 1);
				System.out.print(columnName + "\t");
			}
			System.out.println();
			String tableName = columns.getString("TABLE_NAME");
			String columnName = columns.getString("COLUMN_NAME");
			int columnSize = columns.getInt("COLUMN_SIZE");
			if (columnSize > 0) {
				columnSize = Math.max(columnSize, 50);
			}
			if ("text".equals(type_name)) {
				type_name = "ntext";
			} else {
				columnSize = Math.min(columnSize, 8000);
				type_name = "n" + type_name + "(MAX" + "" + ")";
			}
			String sql = "ALTER TABLE [dbo].[{0}] ALTER COLUMN [{1}] {2} COLLATE Uighur_100_CI_AI;";
			String format = ExtendedMessageFormat.format(sql, tableName, columnName, type_name);
			ps.println(format);
		}
	}

}
